﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Text.RegularExpressions;
using System.Data.OleDb;
using MySql.Data.MySqlClient;
using System.Data.Common;

/// <summary>
/// sql server数据操作的底层类
/// </summary>
public class SQLHelper
{
    public SQLHelper(string conn)
	{
        connectionString = ConfigurationManager.AppSettings[conn];
	}

    //SqlServer
    static String dbType = "SqlServer";
    public static string connectionString = "";
    //public static string connectionString = ConfigurationManager.AppSettings["connOA_DB"];//连接语句，根据情况修改
    
    //Oracle
    //static String dbType = "Oracle";
    //public static String connectionString = "Provider=OraOLEDB.Oracle.1;Data Source=XE;User Id=plus;Password=sa";

    //MySql
    //static String dbType = "MySql";
    //public String connectionString = "server=localhost; user id=root; password=123456; database=plusoft_test;";

    

    private DbConnection conn;
    private DbConnection getConn()
    {
        if (conn == null)
        {
            if (dbType == "MySql")
            {
                conn = new MySqlConnection(connectionString);
            }
            else if (dbType == "Oracle")
            {
                conn = new OleDbConnection(connectionString);
            }
            else if (dbType == "SqlServer")
            {
                conn = new SqlConnection(connectionString);
            }
            //conn.Open();
        }


        return conn;
    }
    //public static void BeginConn()
    //{
    //    getConn();
    //}
    //public static void EndConn()
    //{
    //    if (conn != null)
    //    {
    //        conn.Close();
    //        conn = null;
    //    }
    //}

    public ArrayList Select(string sql)
    {
        return Select(sql, null);
    }
    public ArrayList Select(string sql, Hashtable args)
    {
        DataTable data = ExcuteQuery(sql, args);


        return DataTable2ArrayList(data);
    }

    /// <summary>
    /// 查询命令(sql语句或过程名),返回一个数据表
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public DataTable ExcuteQuery(string sql)
    {
        return ExcuteQuery(sql, null);
    }

    public DataTable ExcuteQuery(string sql, Hashtable args)
    {
        DataTable data = new DataTable();

        bool isConn = conn != null;

        DbConnection con = getConn();

        try
        {
            con.Open();
        }
        catch (Exception e) { }

        using (con)
        {

            if (dbType == "MySql")
            {
                MySqlCommand cmd = new MySqlCommand(sql, (MySqlConnection)con);
                if (args != null) SetArgs(sql, args, cmd);

                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, connectionString);
                adapter.Fill(data);
            }
            else if (dbType == "Oracle")
            {
                OleDbCommand cmd = new OleDbCommand(sql, (OleDbConnection)con);
                if (args != null) SetArgs(sql, args, cmd);

                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, connectionString);
                adapter.Fill(data);
            }
            else if (dbType == "SqlServer")
            {
                SqlCommand cmd = new SqlCommand(sql, (SqlConnection)con);
                //if (args != null) SetArgs(sql, args, cmd);

                SqlDataAdapter adapter = new SqlDataAdapter(sql, connectionString);
                if (args != null) SetArgs(sql, args, adapter);
                //adapter.SelectCommand.Parameters.Add(new SqlParameter());
                adapter.Fill(data);
            }
        }

        return data;
    }

    /// <summary>
    /// 返回首行首列
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public object ExecuteScalar(string sql)
    {
        return ExecuteScalar(sql, null);
    }

    /// <summary>
    /// 返回首行首列
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="args"></param>
    /// <returns></returns>
    public object ExecuteScalar(string sql, Hashtable args)
    {
        bool isConn = conn != null;
        DbConnection con = getConn();
        object obj = null;
        try
        {
            con.Open();
        }
        catch (Exception e) { }

        using (con)
        {

            if (dbType == "MySql")
            {
                MySqlCommand cmd = new MySqlCommand(sql, (MySqlConnection)con);
                if (args != null) SetArgs(sql, args, cmd);
                obj = cmd.ExecuteScalar();
            }
            else if (dbType == "Oracle")
            {
                OleDbCommand cmd = new OleDbCommand(sql, (OleDbConnection)con);
                if (args != null) SetArgs(sql, args, cmd);
                obj = cmd.ExecuteScalar();
            }
            else if (dbType == "SqlServer")
            {
                SqlCommand cmd = new SqlCommand(sql, (SqlConnection)con);
                if (args != null) SetArgs(sql, args, cmd);
                obj = cmd.ExecuteScalar();
            }

        }
        return obj;
        //if (isConn == false)
        //{
        //    EndConn();
        //}
    }

    /// <summary>
    /// 返回影响行数
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public int Execute(string sql)
    {
        return Execute(sql, null);
    }
    /// <summary>
    /// 返回影响行数
    /// </summary>
    /// <param name="sql"></param>
    /// <param name="args"></param>
    /// <returns></returns>
    public int Execute(string sql, Hashtable args)
    {
        bool isConn = conn != null;
        DbConnection con = getConn();
        int i = 0;
        try
        {
            con.Open();
        }
        catch (Exception e) { }

        using (con)
        {

            if (dbType == "MySql")
            {
                MySqlCommand cmd = new MySqlCommand(sql, (MySqlConnection)con);
                if (args != null) SetArgs(sql, args, cmd);
                i = cmd.ExecuteNonQuery();
            }
            else if (dbType == "Oracle")
            {
                OleDbCommand cmd = new OleDbCommand(sql, (OleDbConnection)con);
                if (args != null) SetArgs(sql, args, cmd);
                i = cmd.ExecuteNonQuery();
            }
            else if (dbType == "SqlServer")
            {
                SqlCommand cmd = new SqlCommand(sql, (SqlConnection)con);
                if (args != null) SetArgs(sql, args, cmd);
                i = cmd.ExecuteNonQuery();
            }

        }
        return i;
        //if (isConn == false)
        //{
        //    EndConn();
        //}
    }

    #region 私有
    private void SetArgs(string sql, Hashtable args, SqlDataAdapter adp)
    {
        if (dbType == "SqlServer")
        {
            MatchCollection ms = Regex.Matches(sql, @"@\w+");
            int i = 1;
            foreach (Match m in ms)
            {
                string key = m.Value;

                Object value = args[key];
                if (value == null)
                {
                    value = args[key.Substring(1)];
                }
                if (value == null) value = DBNull.Value;
                // 跳过已定义的参数
                if (!adp.SelectCommand.Parameters.Contains(key))
                {
                    adp.SelectCommand.Parameters.Add(new SqlParameter(key, value));
                }
            }
            //cmd.CommandText = sql;
        }
    }

    private void SetArgs(string sql, Hashtable args, IDbCommand cmd)
    {
        if (dbType == "MySql")
        {
            MatchCollection ms = Regex.Matches(sql, @"@\w+");
            foreach (Match m in ms)
            {
                string key = m.Value;
                string newKey = "?" + key.Substring(1);
                sql = sql.Replace(key, newKey);

                Object value = args[key];
                if (value == null)
                {
                    value = args[key.Substring(1)];
                }

                cmd.Parameters.Add(new MySqlParameter(newKey, value));
            }
            cmd.CommandText = sql;
        }
        else if (dbType == "Oracle")
        {
            MatchCollection ms = Regex.Matches(sql, @"@\w+");
            int i = 1;
            foreach (Match m in ms)
            {
                string key = m.Value;
                string newKey = "@P" + i++;
                sql = sql.Replace(key, "?");

                Object value = args[key];
                if (value == null)
                {
                    value = args[key.Substring(1)];
                }

                cmd.Parameters.Add(new OleDbParameter(newKey, value));
            }
            cmd.CommandText = sql;
        }
        else if (dbType == "SqlServer")
        {
            MatchCollection ms = Regex.Matches(sql, @"@\w+");
            int i = 1;
            foreach (Match m in ms)
            {
                string key = m.Value;

                Object value = args[key];
                if (value == null)
                {
                    value = args[key.Substring(1)];
                }
                if (value == null) value = DBNull.Value;

                cmd.Parameters.Add(new SqlParameter(key, value));
            }
            cmd.CommandText = sql;
        }
    }
    public ArrayList DataTable2ArrayList(DataTable data)
    {
        ArrayList array = new ArrayList();
        for (int i = 0; i < data.Rows.Count; i++)
        {
            DataRow row = data.Rows[i];

            Hashtable record = new Hashtable();
            for (int j = 0; j < data.Columns.Count; j++)
            {
                object cellValue = row[j];
                if (cellValue.GetType() == typeof(DBNull))
                {
                    cellValue = null;
                }
                record[data.Columns[j].ColumnName] = cellValue;
            }
            array.Add(record);
        }
        return array;
    }
    #endregion

}

